package com.ccp.dev.core.basic.table.impl;

import com.ccp.dev.core.basic.db.datasource.JdbcTemplateUtil;
import com.ccp.dev.core.basic.page.PageBean;
import com.ccp.dev.core.basic.table.BaseTableMeta;
import com.ccp.dev.core.basic.table.ColumnModel;
import com.ccp.dev.core.basic.table.SqlTypeConst;
import com.ccp.dev.core.basic.table.TableModel;
import com.ccp.dev.core.basic.table.colmap.OracleColumnMap;
import com.ccp.dev.core.basic.table.colmap.PostgreSqlColumnMap;
import com.ccp.dev.core.util.BeanUtils;
import com.ccp.dev.core.util.StringUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;


/**
 * postgreSql数据库表元数据的读取。
 *
 * @author ray
 */
@Component
public class PostgreSqlTableMeta extends BaseTableMeta {
    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 取得注释
     */
    private String sqlTableComment = "select tablename ,cast(obj_description(relfilenode,'pg_class') as varchar) as comments from " +
            "pg_class c inner join pg_tables t on c.relname = t.tablename where t.schemaname = 'public' and t.tablename ='%s'";

    private final String SQL_GET_COLUMNS = "select" +
            " table_name,column_name,is_nullable,data_type,character_octet_length length," +
            " numeric_precision precisions,numeric_scale scale" +
            " from " +
            " information_schema.columns " +
            " where table_schema='public' and table_name='%s'";

    private final String SQL_GET_COLUMNS_BATCH = "select" +
            " table_name,column_name,is_nullable,data_type,character_octet_length length," +
            " numeric_precision precisions,numeric_scale scale" +
            " from " +
            " information_schema.columns " +
            " where table_schema='public'";

    /**
     * 查询某个表所有列的注释
     */
    private final String DESCRIPT_SQL = "select a.attname, description from pg_catalog.pg_description d,pg_catalog.pg_attribute a where d.objoid=a.attrelid" +
            " and d.objsubid=a.attnum and a.attrelid=(select oid from pg_class where relname='%s') and a.attnum>0";

    /**
     * 查询某个表主键
     */
    private final String PK_SQL = "select pg_attribute.attname as colname,pg_constraint.conname as pk_name from" +
            "  pg_constraint  inner join pg_class" +
            "  on pg_constraint.conrelid = pg_class.oid" +
            "  inner join pg_attribute on pg_attribute.attrelid = pg_class.oid" +
            "  and  pg_attribute.attnum = pg_constraint.conkey[1]" +
            "  inner join pg_type on pg_type.oid = pg_attribute.atttypid" +
            "  where pg_class.relname = '%s'" +
            "  and pg_constraint.contype='p'";

    /**
     * 取得数据库所有表
     */
    private String sqlAllTables = "select tablename as table_name,cast(obj_description(relfilenode,'pg_class') as varchar) as comments from " +
            "pg_class c inner join pg_tables t on c.relname = t.tablename where t.schemaname = 'public' ";

    /**
     * 根据表名查询列表，如果表名为空则去系统所有的数据库表。
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    @Override
    public Map<String, String> getTablesByName(String tableName) {
        String sql = sqlAllTables;
        if (StringUtil.isNotEmpty(tableName)) {
            sql = sqlAllTables + " and  tablename like '%" + tableName.toLowerCase() + "%'";
        }
        Map parameter = new HashMap();
        List list = JdbcTemplateUtil.getNamedParameterJdbcTemplate(jdbcTemplate).query(sql, parameter, new RowMapper<Map<String, String>>() {

            @Override
            public Map<String, String> mapRow(ResultSet rs, int row)
                    throws SQLException {
                String tableName = rs.getString("table_name");
                String comments = rs.getString("comments");
                Map<String, String> map = new HashMap<String, String>();
                map.put("name", tableName);
                map.put("comments", comments);
                return map;
            }
        });
        Map<String, String> map = new LinkedHashMap<String, String>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, String> tmp = (Map<String, String>) list.get(i);
            String name = tmp.get("name");
            String comments = tmp.get("comments");
            map.put(name, comments);
        }

        return map;
    }

    /**
     * 获取表对象
     */
    @Override
    public TableModel getTableByName(String tableName) {
        tableName = tableName.toLowerCase();
        TableModel model = getTableModel(tableName);
        //获取列对象
        List<ColumnModel> columnList = getColumnsByTableName(tableName);
        model.setColumnList(columnList);
        return model;
    }


    /**
     * 根据表名获取tableModel。
     *
     * @param tableName
     * @return
     */
    private TableModel getTableModel(final String tableName) {

        String sql = String.format(sqlTableComment, tableName);
        TableModel tableModel = jdbcTemplate.queryForObject(sql, null, new RowMapper<TableModel>() {

            @Override
            public TableModel mapRow(ResultSet rs, int row)
                    throws SQLException {
                TableModel tableModel = new TableModel();
                tableModel.setName(tableName);
                tableModel.setComment(rs.getString("comments"));
                return tableModel;
            }
        });
        if (BeanUtils.isEmpty(tableModel)) {
            tableModel = new TableModel();
        }

        return tableModel;
    }

    /**
     * 根据表名获取列
     *
     * @param tableName 表名
     * @return
     */
    private List<ColumnModel> getColumnsByTableName(String tableName) {
        String sql = String.format(SQL_GET_COLUMNS, tableName);
        Map<String, Object> map = new HashMap<>();
        List<ColumnModel> columnList = JdbcTemplateUtil.getNamedParameterJdbcTemplate(jdbcTemplate).query(sql, map, new PostgreSqlColumnMap());
        //查询表字段注释
        String descriptionSql = String.format(DESCRIPT_SQL, tableName);
        List<Map<String, Object>> descriptionList = jdbcTemplate.queryForList(descriptionSql);
        //查询表主键
        String pkSql = String.format(PK_SQL, tableName);
        List<Map<String, Object>> pkSqlList = jdbcTemplate.queryForList(pkSql);
        for (ColumnModel columnModel : columnList) {
            String name = columnModel.getName();
            for (Map<String, Object> descriptMap : descriptionList) {
                if (name.equals(descriptMap.get("attname"))) {
                    columnModel.setComment(descriptMap.get("description").toString());
                }
            }
        }
        //设置表主键字段
        for (ColumnModel columnModel : columnList) {
            String name = columnModel.getName();
            for (Map<String, Object> pkMap : pkSqlList) {
                if (name.equals(pkMap.get("colname"))) {
                    columnModel.setIsPk(true);
                }
            }
        }
        return columnList;
    }

    @Override
    public Map<String, String> getTablesByName(List<String> names) {
        StringBuffer sb = new StringBuffer();
        for (String name : names) {
            sb.append("'");
            sb.append(name);
            sb.append("',");
        }
        sb.deleteCharAt(sb.length() - 1);
        String sql = sqlAllTables + " and  lower(table_name) in (" + sb.toString().toLowerCase() + ")";


        Map parameter = new HashMap();
        List list = JdbcTemplateUtil.getNamedParameterJdbcTemplate(jdbcTemplate).query(sql, parameter, new RowMapper<Map<String, String>>() {

            @Override
            public Map<String, String> mapRow(ResultSet rs, int row)
                    throws SQLException {
                String tableName = rs.getString("TABLE_NAME");
                String comments = rs.getString("COMMENTS");
                Map<String, String> map = new HashMap<String, String>();
                map.put("NAME", tableName);
                map.put("COMMENTS", comments);
                return map;
            }
        });
        Map<String, String> map = new LinkedHashMap<String, String>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, String> tmp = (Map<String, String>) list.get(i);
            String name = tmp.get("NAME");
            String comments = tmp.get("COMMENTS");
            map.put(name, comments);
        }

        return map;
    }

    @Override
    public List<TableModel> getTablesByName(String tableName, PageBean pageBean)
            throws Exception {
        String sql = sqlAllTables;

        if (StringUtil.isNotEmpty(tableName)) {
            sql += " AND  LOWER(table_name) LIKE '%" + tableName.toLowerCase() + "%'";
        }
        RowMapper<TableModel> rowMapper = new RowMapper<TableModel>() {

            @Override
            public TableModel mapRow(ResultSet rs, int row)
                    throws SQLException {
                TableModel tableModel = new TableModel();
                tableModel.setName(rs.getString("TABLE_NAME"));
                tableModel.setComment(rs.getString("COMMENTS"));
                return tableModel;
            }
        };
        List<TableModel> tableModels = getForList(sql, pageBean, rowMapper, SqlTypeConst.POSTGRESQL);
        List<String> tableNames = new ArrayList<String>();
        //get all table names
        for (TableModel model : tableModels) {
            tableNames.add(model.getName());
        }
        //batch get table columns
        Map<String, List<ColumnModel>> tableColumnsMap = getColumnsByTableName(tableNames);
        //extract table columns from paraTypeMap by table name;
        for (Map.Entry<String, List<ColumnModel>> entry : tableColumnsMap.entrySet()) {
            //set TableModel's columns
            for (TableModel model : tableModels) {
                if (model.getName().equalsIgnoreCase(entry.getKey())) {
                    model.setColumnList(entry.getValue());
                }
            }
        }

        return tableModels;
    }

    /**
     * 根据表名获取列。此方法使用批量查询方式。
     *
     * @param tableNames
     * @return
     */
    @SuppressWarnings("unchecked")
    private Map<String, List<ColumnModel>> getColumnsByTableName(List<String> tableNames) {
        String sql = SQL_GET_COLUMNS_BATCH;
        Map<String, List<ColumnModel>> map = new HashMap<String, List<ColumnModel>>();
        if (tableNames != null && tableNames.size() == 0) {
            return map;
        } else {
            StringBuffer buf = new StringBuffer();
            for (String str : tableNames) {
                buf.append("'" + str + "',");
            }
            buf.deleteCharAt(buf.length() - 1);
            sql += " AND A.TABLE_NAME IN (" + buf.toString() + ") ";
        }

        Long b = System.currentTimeMillis();
        List<ColumnModel> columnModels = JdbcTemplateUtil.getNamedParameterJdbcTemplate(jdbcTemplate).query(sql, new HashMap<String, Object>(), new OracleColumnMap());

        for (ColumnModel columnModel : columnModels) {
            String tableName = columnModel.getTableName();
            if (map.containsKey(tableName)) {
                map.get(tableName).add(columnModel);
            } else {
                List<ColumnModel> cols = new ArrayList<ColumnModel>();
                cols.add(columnModel);
                map.put(tableName, cols);
            }
        }
        return map;
    }

    @Override
    public String getAllTableSql() {
        return sqlAllTables;
    }

}
